using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SalesAndStoreApp.DAL.DAO;

namespace SalesAndStoreApp.DAL.Gateway
{
    public class InventoryGateway:DbGateway
    {
        public string InsertOrUpdate(List<Product> products)
        {
           products = UpdateInventoryForExistingProducts(products);
            InsertInInventoryForNewProducts(products);
            return "Inventory insertion succeed";
        }

        public string InsertOrUpdate(Product product)
        {
            if (HasThisProductToInventory(product.Id))
            {
                UpdateInventoryForAProduct(product);
            }
            else
            {
                InsertInInventoryForANewProducts(product);
            }
            return "Inventory insertion succeed";
        }
            
        private bool HasThisProductToInventory(int id)
        {
            try
            {
                string query = "SELECT Product_Id FROM Inventory WHERE Product_Id=@productId";
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@productId", SqlDbType.Int);
                Command.Parameters["@productId"].Value = id;
                Connection.Open();
                int count = 0;
                using (SqlDataReader dataReader = Command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        count++;
                    }
                }
                return count > 0;

            }
            catch (Exception exception)
            {
               throw new Exception("System Error while trying to operate some checking operation on inventory",exception);
            }
            finally
            {
                CloseConnection(Connection);
            }
        }

        private bool InsertInInventoryForANewProducts(Product product)
        {
            try
            {
                Connection.Open();
                string query = "INSERT INTO Inventory VALUES(@productId,@quantity,@reOrderLevel)";
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@productId", SqlDbType.Int);
                Command.Parameters["@productId"].Value = product.Id;
                Command.Parameters.Add("@quantity", SqlDbType.Int);
                Command.Parameters["@quantity"].Value = product.Inventory.Quantity;
                Command.Parameters.Add("@reOrderLevel", SqlDbType.Int);
                Command.Parameters["@reOrderLevel"].Value = product.Inventory.ReOrderLevel;
                Command.ExecuteNonQuery();
                return true;
            }
            catch (Exception exception)
            {
                throw new Exception("System Error Occurred during tried to inventory insertion process for a product",exception);
            }
            finally
            {
                CloseConnection(Connection);
            }
        }

        private bool InsertInInventoryForNewProducts(List<Product> products)
        {
            Connection.Open();
            string query = "INSERT INTO Inventory VALUES(@productId,@quantity,@reOrderLevel)";
            Command.CommandText = query;
            try
            {
                foreach (var product in products)
                {
                    Command.Parameters.Clear();
                    Command.Parameters.Add("@productId", SqlDbType.Int);
                    Command.Parameters["@productId"].Value = product.Id;
                    Command.Parameters.Add("@quantity", SqlDbType.Int);
                    Command.Parameters["@quantity"].Value = product.Inventory.Quantity;
                    Command.Parameters.Add("@reOrderLevel", SqlDbType.Int);
                    Command.Parameters["@reOrderLevel"].Value = product.Inventory.ReOrderLevel;
                    Command.ExecuteNonQuery();
                }
                return true;
               
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred while trying to insert products in inventory",exception);
            }
            finally
            {
                CloseConnection(Connection);

            }

        }

        private List<Product> UpdateInventoryForExistingProducts(List<Product> products)
        {
            Connection.Open();
            try
            {
                List<Product> returnProducts = new List<Product>();
                returnProducts.AddRange(products);
                string query = "Update Inventory SET Quantity = Quantity+@qty, ReOrderLevel = @reorderLevel WHERE Product_Id=@productId";
                Command.CommandText = query;

                foreach (var product in products)
                {
                    Command.Parameters.Clear();
                    Command.Parameters.Add("@qty", SqlDbType.Int);
                    Command.Parameters["@qty"].Value = product.Inventory.Quantity;
                    Command.Parameters.Add("@productId", SqlDbType.Int);
                    Command.Parameters["@productId"].Value = product.Id;
                    Command.Parameters.Add("@reorderLevel", SqlDbType.Int);                 
                    Command.Parameters["@reorderLevel"].Value = product.Inventory.ReOrderLevel;
                    int check = Command.ExecuteNonQuery();
                    if (check>0)
                    {
                        returnProducts.Remove(product);
                    }
                }
                return returnProducts;

            }
            catch (Exception exception)
            {
                
                throw new Exception("Error Occured while populating existing product on inventory!",exception);
            }
            finally
            {
                CloseConnection(Connection);

            }
        }

        public void InsertOrDeduct(ProductCarriage productCarriage)
        {
            productCarriage.Products = DeductExistingProductsQuantity(productCarriage.Products);
            InsertBorrowedProducts(productCarriage.Products);
        }

        private bool InsertBorrowedProducts(List<Product> products)
        {
            Connection.Open();
            string query = "INSERT INTO Inventory VALUES(@productId,@quantity,@reorderLevel)";
            Command.CommandText = query;
            try
            {
                foreach (var product in products)
                {
                    Command.Parameters.Clear();
                    Command.Parameters.Add("@productId", SqlDbType.Int);
                    Command.Parameters["@productId"].Value = product.Id;
                    Command.Parameters.Add("@quantity", SqlDbType.Int);
                    Command.Parameters["@quantity"].Value = -product.Inventory.Quantity;
                    Command.Parameters.Add("@reOrderLevel", SqlDbType.Int);
                    Command.Parameters["@reOrderLevel"].Value = product.Inventory.ReOrderLevel;
                    Command.ExecuteNonQuery();
                }
                return true;

            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred while trying to insert products in inventory", exception);
            }
            finally
            {
               CloseConnection(Connection);

            }
        }

        private List<Product> DeductExistingProductsQuantity(List<Product> products)
        {
            Connection.Open();
            try
            {
                List<Product> returnProducts = new List<Product>();
                returnProducts.AddRange(products);
                string query = "Update Inventory SET Quantity = Quantity-@qty WHERE Product_Id=@productId";
                Command.CommandText = query;

                foreach (var product in products)
                {
                    Command.Parameters.Clear();
                    Command.Parameters.Add("@qty", SqlDbType.Int);
                    Command.Parameters["@qty"].Value = product.Inventory.Quantity;
                    Command.Parameters.Add("@productId", SqlDbType.Int);
                    Command.Parameters["@productId"].Value = product.Id;
                    int check = Command.ExecuteNonQuery();
                    if (check > 0)
                    {
                        returnProducts.Remove(product);
                    }
                }
                return returnProducts;

            }
            catch (Exception exception)
            {

                throw new Exception("Error Occured while populating existing product on inventory!", exception);
            }
            finally
            {
                CloseConnection(Connection);

            }
        }

        

       

        public Inventory GetInventoryOfProduct(int id)
        {
            Inventory inventory = new Inventory();
            string query = "SELECT * FROM Inventory WHERE Product_Id=@productId";
            Command.CommandText = query;
            Command.Parameters.Clear(); 
            Command.Parameters.Add("@productId", SqlDbType.Int);
            Command.Parameters["@productId"].Value = id;
            Connection.Open();
            using (SqlDataReader dataReader =Command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    inventory.Id = int.Parse(dataReader["Id"].ToString());
                    inventory.Quantity = int.Parse(dataReader["Quantity"].ToString());
                    inventory.ReOrderLevel = int.Parse(dataReader["ReOrderLevel"].ToString());
                }
            }
            Connection.Close();
            return inventory;
        }

        public bool UpdateInventoryForAProduct(Product product)
        {
            try
            {
                Connection.Open();
                string query = "Update Inventory SET Quantity = Quantity+@qty, ReOrderLevel = @reorderLevel WHERE Product_Id=@productId";
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@qty", SqlDbType.Int);
                Command.Parameters["@qty"].Value = product.Inventory.Quantity;
                Command.Parameters.Add("@productId", SqlDbType.Int);
                Command.Parameters["@productId"].Value = product.Id;
                Command.Parameters.Add("@reorderLevel", SqlDbType.Int);
                Command.Parameters["@reorderLevel"].Value = product.Inventory.ReOrderLevel;
                Command.ExecuteNonQuery();
                return true;
            }
            catch (Exception exception)
            {
                throw new Exception("System Error Occurred while trying to update inventory for a product",exception);
            }
        }
    }
}